package com.apes.hr.group.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.group.model.DingDingDept;
import com.apes.scm.masterdata.frame.model.Branch;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * @author ZhengZeXian
 * @create 2020年9月10日09:58:27
 */
public interface DingDingDeptRepository extends CustomRepository<DingDingDept, String> {

    DingDingDept findByName(String name);

    //根据运营模式查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where dd.operation = :operation and dd.branch_id is null and dd.work_post_id is null and dd.company_id is null",nativeQuery = true)
    List<DingDingDept> findByOpertion(@Param("operation") String operation);

    //根据公司查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where dd.company_id = :company and dd.branch_id is null and dd.work_post_id is null and dd.operation is null",nativeQuery = true)
    List<DingDingDept> findByCompany(@Param("company") String company);

    //根据行政部门查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id " +
            "where :branch in (select id from mas_branch b start with b.parents_id = dd.branch_id connect by prior b.id = b.parents_id)" +
            " and dd.work_post_id is null and dd.operation is null and dd.company_id is null " +
            "or dd.branch_id = :branch and dd.work_post_id is null and dd.operation is null and dd.company_id is null",nativeQuery = true)
    List<DingDingDept> findByBranch(@Param("branch") String branch);

    //根据运营模式+岗位查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where dd.operation = :operation and dd.work_post_id = :workPost and dd.branch_id is null and dd.company_id is null",nativeQuery = true)
    List<DingDingDept> findByOperationAndWorkPost(@Param("operation") String operation, @Param("workPost") String workPost);

    //根据公司+岗位查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where dd.company_id = :company and dd.work_post_id = :workPost and dd.branch_id is null and dd.operation is null",nativeQuery = true)
    List<DingDingDept> findByCompanyAndWorkPost(@Param("company") String company, @Param("workPost") String workPost);

    //根据行政部门+岗位查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where :branch in (select id from mas_branch b start with b.parents_id = dd.branch_id connect by prior b.id = b.parents_id) and dd.work_post_id = :workPost or dd.branch_id = :branch and dd.work_post_id = :workPost",nativeQuery = true)
    List<DingDingDept> findByBranchAndWorkPost(@Param("branch") String branch, @Param("workPost") String workPost);

    //根据岗位查找对应钉钉部门
    @Query(value = "select * from hr_ding_ding_dept d left join hr_ding_ding_dept_detail dd on dd.ding_ding_dept_id = d.id where dd.work_post_id = :workPost and dd.operation is null and dd.branch_id is null and dd.company_id is null",nativeQuery = true)
    List<DingDingDept> findByWorkPost(@Param("workPost") String workPost);

    //根据非规则上的行政部门查找钉钉部门
    List<DingDingDept> findByBranch(Branch branch);
}
